from typing import List, Optional
from sqlmodel import text, and_, select, func
from fastapi import status
from .base import BaseService
from app import models
from app.models.flight import Carrier, Location, Destination, FlightQueryCondition, FlightQueryPaginatedResponse, FlightInfo,   FlightList


class FlightService(BaseService):
    """航班服务类"""

    def __init__(self, db, current_user):
        super().__init__(db, current_user)
        

    async def get_carriers(self) -> List[Carrier]:
        """
        从数据库中获取所有航空公司的名称列表
        
        Returns:
            航空公司名称列表
        """
        # SQL查询语句
        sql = text("SELECT fc.carrier FROM focus_company fc")
        
        # 执行查询
        result = self.db.exec(sql)
        
        # 将结果转换为Carrier对象列表
        carriers = []
        for row in result.all():
            carrier_dict = row._asdict()
            carriers.append(Carrier(**carrier_dict))
            
        return carriers

    async def get_departures(self) -> List[Location]:
        """
        从数据库中获取所有出发地列表
        
        Returns:
            出发地列表
        """
        # SQL查询语句
        sql = text("SELECT fc.dept FROM air_ticket.t_policys_dept_arr fc GROUP BY fc.dept")
        
        # 执行查询
        result = self.db.exec(sql)
        
        # 将结果转换为Location对象列表
        departures = []
        for row in result.all():
            location_dict = row._asdict()
            departures.append(Location(**location_dict))
            
        return departures

    async def get_destinations(self) -> List[Destination]:
        """
        从数据库中获取所有目的地列表
        
        Returns:
            目的地列表
        """
        # SQL查询语句
        sql = text("SELECT fc.arr FROM air_ticket.t_policys_dept_arr fc GROUP BY fc.arr")
        
        # 执行查询
        result = self.db.exec(sql)
        
        # 将结果转换为Destination对象列表
        destinations = []
        for row in result.all():
            destination_dict = row._asdict()
            destinations.append(Destination(**destination_dict))
            
        return destinations

    
    async def query_flights_price_by_id(self, id: str) -> models.FlightQueryResponse:
        """
        根据ID查询航班信息
        
        Args:
            id: 航班记录ID
            
        Returns:
            符合ID的航班信息响应
        """
        # 统一异常处理
        try:
            # 构建根据ID查询的SQL语句
            sql = text("""select ffp.id,ffp.price ,ffp.createtime 
,case when pp.prob_decrease  is not null and pp.prob_decrease>=0.70 then 1 else 0 end as decrease_flag
from air_ticket.t_focus_flight_fill_price ffp
left join air_ticket.price_predict_prob pp on ffp.id=pp.id and ffp.createtime =pp.predicttime 
where ffp.id = :id order by createtime""")
            
            # 执行查询
            result = self.db.exec(sql.params(id=id))
            flight_data_list = result.all()
            
            # 如果没有找到数据，返回404响应
            if not flight_data_list:
                return models.FlightQueryResponse(
                    code=404,
                    message="未找到ID为 {} 的航班记录".format(id),
                    data=None
                )
            
            # 将结果转换为PolicysFillFocus对象列表
            flight_info_list = []
            for flight_data in flight_data_list:
                flight_dict = flight_data._asdict()
                # 处理Decimal类型转换为float
                if 'price' in flight_dict and flight_dict['price'] is not None:
                    flight_dict['price'] = float(flight_dict['price'])
                flight_info = models.PolicysFillFocus(**flight_dict)
                flight_info_list.append(flight_info)
                
            return models.FlightQueryResponse(
                code=0,
                message="检索成功",
                data=flight_info_list  # 返回所有匹配的记录
            )
        except Exception as e:
            return models.FlightQueryResponse(
                code=500,
                message="查询失败: {}".format(str(e)),
                data=None
            )
            
           

    async def query_simple_flights(self, condition: FlightQueryCondition) -> List[FlightList]:
        """
        根据条件查询简单航班信息（使用 t_flight_list 表）
        
        Args:
            condition: 查询条件
            
        Returns:
            符合条件的简单航班信息列表
        """
        # 定义字段映射配置（只包含 t_flight_list 表中存在的字段）
        field_mappings = {
            # 文本字段
            "dept": {
                "field": "dept",
                "type": "text",
                "exact_match": True
            },
            "carrier": {
                "field": "carrier",
                "type": "text",
                "exact_match": True
            },
            "arr": {
                "field": "arr",
                "type": "text",
                "exact_match": True
            },
            "fno": {
                "field": "fno",
                "type": "text",
                "exact_match": True
            },
            
            # 出发日期范围字段
            "depttime": {
                "field": "depttime",
                "type": "date",
                "start_field": "depttime_start",
                "end_field": "depttime_end"
            }
        }
        
        try:
            # 构建查询条件
            self.model=FlightList
            conditions = self.build_conditions_from_payload(condition, field_mappings)
            
            # 执行分页查询
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=FlightList.depttime,
                descending=False,
                pagination=condition
            )
            
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return FlightQueryPaginatedResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的航班数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=FlightQueryPaginatedResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return FlightQueryPaginatedResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=condition.current_page,
                    page_size=condition.page_size,
                    total=0,
                    total_pages=0
                )
            )
